/**

This file is part of MaCI/GIMnet.

MaCI/GIMnet is free software: you can redistribute it and/or modify it 
under the terms of the GNU Lesser General Public License as published 
by the Free Software Foundation, either version 3 of the License, or 
(at your option) any later version.

MaCI/GIMnet is distributed in the hope that it will be useful, but WITHOUT 
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 
FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public 
License for more details.

You should have received a copy of the GNU Lesser General Public 
License along with GIMnet. (See COPYING.LESSER) If not, see 
<http://www.gnu.org/licenses/>.

**/
/**
 * \file
 *
 * \brief Handling Motti Map objects in SQL DB.
 * \author Antti Maula <antti.maula@tkk.fi>
 */
#ifndef _MACI_INTERFACE_SQL_EXTENSION_MOTTIMAPOBJECTDB_HPP_
#define _MACI_INTERFACE_SQL_EXTENSION_MOTTIMAPOBJECTDB_HPP_

#include "SQLQuery.hpp"
#include "SQLResult.hpp"
#include "SQLClient.hpp"
#include "SQLResultTypes.hpp"
#include "SQLExtension.hpp"
#include <string>
#include <vector>
#include <sstream>
#include <iostream>

namespace MaCI {
  namespace SQL {
    namespace Ext {
      
      /** Single row of MottiMapObjectDB type.
       */
      struct SMottiMapObjectRow
      {
      public:
        /** Empty constructor.
         */
        SMottiMapObjectRow(void) 
          : id(), group_id(), logical_id(), type_id(), x(), y() 
        {}
        
        
        /** Constructor accepting single TSQLRow.
         */
        SMottiMapObjectRow(const TSQLRow &aRow)
          : id(), group_id(), logical_id(), type_id(), x(), y() 
        {
          DecodeSQLResultRow(aRow);
        }


        /** Construct by data.
         *
         */
        SMottiMapObjectRow(const unsigned int aGroupID, 
                           const unsigned int aLogicalID, 
                           const unsigned int aTypeID, 
                           const float aX, const float aY)
          : id(), 
            group_id(aGroupID),
            logical_id(aLogicalID), 
            type_id(aTypeID), 
            x(aX), 
            y(aY) 
        {}
        

        /** Destructor.
         */
        ~SMottiMapObjectRow() 
        {
        }

        
        /** This function attempts to decode a SQL Row as type
         * SMottiMapObjectRow.
         *
         * This function takes a TSQLRow as parameter, and attempts to
         * decode all fields to current container. This function will
         * fail if 1) the SQLRow size doesn't match required size of
         * 11 fields.  2) if any of the integer type fields doesn't
         * correctly convert to integer.
         *
         * \note This Decoder expects to get ALL available fields. If
         * the query is made so, that result set DOESN'T contain all
         * the available fields (by selecting fields), a special
         * processing function needs to be written. (Or contents be
         * taken care of manually)
         *
         * @param[in] aRow      TSQLRow container to decode as 
         *                      SMottiMapObjectRow.
         * @return              'true' if all conditions mentioned 
         *                      above are met, otherwise 'false'.
         */
        bool DecodeSQLResultRow(const TSQLRow &aRow) {
          bool result = false;

          // This is not a magic value, this is the number of fields
          // in row. Very static. Rows without 'result' check are fields
          // that can be NULL. (Not very good handling though)
          if (aRow.size() == 6) {
            result = aRow.at(0).ConvertTo(id);
            result &= aRow.at(1).ConvertTo(group_id);
            result &= aRow.at(2).ConvertTo(logical_id);
            result &= aRow.at(3).ConvertTo(type_id);
            result &= aRow.at(4).ConvertTo(x);
            result &= aRow.at(5).ConvertTo(y);
          }
          return result;
        }

        inline unsigned int GetID() const {
          return id;
        }
        inline unsigned int GetGroupID() const {
          return group_id;
        }
        inline unsigned int GetLogicalID() const {
          return logical_id;
        }
        inline unsigned int GetTypeID() const {
          return type_id;
        }
        inline float GetX() const {
          return x;
        }
        inline float GetY() const {
          return y;
        }

        std::string ToString(void) const {
          std::stringstream s;
          s << "<";
          s << "id:" << id << ", ";
          s << "group_id:" << group_id << ", ";
          s << "logical_id:" << logical_id << ", ";
          s << "type_id:" << type_id << ", ";
          s << "x:" << x << ", ";
          s << "y:" << y;
          s << ">";
          return s.str();
        }

      public:
        unsigned int id;         ///< SQL Identifier of Row
        unsigned int group_id;   ///< Group ID.
        unsigned int logical_id; ///< Logical ID.
        unsigned int type_id;    ///< Type ID
        float x;                 ///< X coordinate
        float y;                 ///< Y coordinate
      };


      /** Array of SMottiMapObjectRow elements.
       */
      typedef std::vector<SMottiMapObjectRow> TMottiMapObjectResultTable;


      /** Helper class for handling MottiMapObjectDB storage format.
       * 
       * This is a helper class for handling 'MaCI Service' storage
       * format over the SQL interface. This storage format is not
       * directly bound to any existing interface, and hence is a good
       * example/starting point for a 'SQL Extension module'
       *
       * \note This helper class is 'Header Only' implementation.
       *
       * \see MaCI::SQL::Ext namespace for more details on helper
       * classes.
       */
      class CMottiMapObjectDB : public CSQLExtension
      {
      public:
        /** Constructor.
         *
         * @param[in] aSQLClient Pointer to initialized and connect
         * SQLClient instance. This instance is used for communicating
         * with the SQL interface.
         * @param[in] aTableName Table name to query for.
         *
         */
        CMottiMapObjectDB(CSQLClient *aSQLClient, const std::string &aTargetTable)
          : CSQLExtension(aSQLClient, aTargetTable) { 
        }

        /** Destructor.
         */
        ~CMottiMapObjectDB(void) {
        }
       

        /** Create table with MottiMapObject DB schema.
         *
         * This function attempts to create a new table with
         * MottiMapObject layout. If the table exists or cannot be
         * created, this function returns NULL. This function uses the
         * internally set TargetTable, so be sure to set the target
         * table before attempting to call this.
         *
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         *
         */
        bool CreateTable(const unsigned int aTimeout_ms = 20000);


        /** Drop table.
         *
         * This function Drops the table currently set at TargetTable.
         *
         * \note This function may drop ANY table currently set to
         * TargetTable at which the current user has database access
         * right 'DROP' to.
         *
         * \note BEWARE: DESTROYS ALL STORED DATA!
         *
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         *
         */
        bool DropTable(const unsigned int aTimeout_ms = 20000);


        /** Insert new MottiMapObject Row.
         *
         * This functions inserts a new entry in the
         * database. Currently there is no function for adding
         * multiple entries with one call, but such may be added if
         * the performance hit from single insert is seen too big.
         * 
         * @param[in] aNewRow   New row to insert.
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         *
         */
        bool InsertMottiMapObjectDBRow(const SMottiMapObjectRow &aNewRow, 
                                       const unsigned int aTimeout_ms = 20000);


        /** Get MottiMapObjects from database.
         *
         * This function fetches MottiMapObjects from the database
         * using the given Where clause. Limits can also be given with
         * the offset and RowCount parameters. Number of returned rows
         * can be examined with standard 'size()' call on the
         * resultarray.
         *
         * @param[out] aResultArray Result array is stored here.
         * @param[in] aWhere    Where clause to use. 
         *                      The given clause is inserted as 'WHERE <clause>'
         * @param[in] aOffset   Limit offset (first entry of search to return)
         * @param[in] aRowCount Number of rows to return starting from aOffset
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         * 
         */
        bool GetMottiMapObjects(TMottiMapObjectResultTable &aResultArray,
                                const std::string &aWhere,
                                const unsigned int aOffset = 0,
                                const unsigned int aRowCount = 1000,
                                const unsigned int aTimeout_ms = 20000);

      private:
        CMottiMapObjectDB(const CMottiMapObjectDB &) 
          : CSQLExtension() {}
        CMottiMapObjectDB &operator=(const CMottiMapObjectDB &) { return *this; }
      };
      
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////

      bool CMottiMapObjectDB::DropTable(const unsigned int aTimeout_ms)
      {
        bool result = false;
        std::stringstream querys;
        querys << "DROP TABLE `" + iTargetTable + "`" << std::endl;  
        
        // Convert to standard string
        const std::string query(querys.str());
        
        // Execute
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          // Check that result contains a table.
          if (sqlresult.GotAffectedRows()) {
            dPrint(ODDEBUG(1),"Affected rows %lld, all OK.", sqlresult.GetAffectedRows());
            result = true;
            
          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;
            
          } else {
            iLastError = "Unknown error!";
            result = false;
            
          }
          
        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
          
        }
        return result;
      }

      bool CMottiMapObjectDB::CreateTable(const unsigned int aTimeout_ms)
      {
        bool result = false;
        std::string database = "motti";
        std::string table = "mapObjects";
        std::string command;
        std::stringstream finals;

        // Create SQL clause
        finals << "CREATE TABLE `" + iTargetTable + "` (" << std::endl;
        finals << "`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ," << std::endl;
        finals << "`group_id` INT NOT NULL ," << std::endl;
        finals << "`logical_id` INT NOT NULL ," << std::endl;
        finals << "`type_id` INT NOT NULL ," << std::endl;
        finals << "`x` FLOAT NOT NULL ," << std::endl;
        finals << "`y` FLOAT NOT NULL ," << std::endl;
        finals << "INDEX ( `logical_id` )" << std::endl;
        finals << ") ENGINE = MYISAM" << std::endl;
        
        // Convert to standard string
        const std::string query(finals.str());

        // Execute
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          // Check that result contains a table.
          if (sqlresult.GotAffectedRows()) {
            dPrint(ODDEBUG(1),"Affected rows %lld, all OK.", sqlresult.GetAffectedRows());
            result = true;

          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result;
      }

      bool CMottiMapObjectDB::InsertMottiMapObjectDBRow(const SMottiMapObjectRow &aNewRow, const unsigned int aTimeout_ms)
      {
        bool result = true;
        std::stringstream querys;
        querys << "INSERT INTO `" << iTargetTable << "` (" << std::endl <<
          "`id` ," << std::endl <<
          "`group_id` ," << std::endl <<
          "`logical_id` ," << std::endl <<
          "`type_id` ," << std::endl <<
          "`x` ," << std::endl <<
          "`y`" << std::endl <<
          ")" << std::endl <<
          "VALUES (" << std::endl <<
          "'', '" << aNewRow.group_id << 
          "', '" << aNewRow.logical_id << 
          "', '" << aNewRow.type_id << 
          "', '" << aNewRow.x << 
          "', '" << aNewRow.y << 
          "'" << std::endl <<
          ");" << std::endl;
        
        std::string query(querys.str());
        //        dPrint(ODTEST,"Query:\n'%s'", query.c_str());
        
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          // Check that result contains a table.
          if (sqlresult.GotAffectedRows()) {
            dPrint(ODDEBUG(1),"Affected rows %lld, all OK.", sqlresult.GetAffectedRows());
            result = sqlresult.GetAffectedRows() == 1;

          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result;
      }
      
      bool CMottiMapObjectDB::GetMottiMapObjects(TMottiMapObjectResultTable &aResultArray,
                                                 const std::string &aWhere,
                                                 const unsigned int aOffset,
                                                 const unsigned int aRowCount,
                                                 const unsigned int aTimeout_ms) 
      {
        // Create and construct stream objects (even though a lot
        // slower than sprintf conversion, use this)
        std::stringstream offsetStr; offsetStr << aOffset;
        std::stringstream rowcountStr; rowcountStr << aRowCount;
        
        // Construct Query
        const std::string query("SELECT `id`,`group_id`,`logical_id`,`type_id`,`x`,`y` FROM " + 
                                iTargetTable + " " + 
                                "WHERE " + aWhere +
                                " LIMIT " + 
                                offsetStr.str() + 
                                "," + 
                                rowcountStr.str()); 
        
        // Execute Query.
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        bool result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          aResultArray.clear();
          
          // Check that result contains a table.
          if (sqlresult.GotTable()) {
            
            // Get table reference for easier (faster) access.
            const CSQLTable &t = sqlresult.GetTable();
            
            // Get rowcount and iterate rows.
            const unsigned int rowcount = t.GetRowCount();
            
            // Loop through all rows, watch those results!
            for (unsigned int i = 0; 
                 i < rowcount && result; 
                 ++i) {
              aResultArray.push_back(SMottiMapObjectRow(t.GetRow(i)));
            }

            result = true;
          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result;
      }
      ///////////////////////////////////////////////////////////////////

      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
    }
  }
}
#endif // _MACI_INTERFACE_SQL_EXTENSION_MOTTIMAPOBJECTDB_HPP_
